from django.shortcuts import render
from django.http import HttpResponse
from .models import Student,Teacher,Course,Score
from django.db.models import Avg,Sum,Count,Q
from django.db import connection
# Create your views here.
def index(request):
    students = Student.objects.annotate(score_avg=Avg("score__number")).filter(score_avg__gt=60).values('id','score_avg')
    for student in students:
        print(student)
    print(connection.queries)
    return HttpResponse("index ok")

def index1(request):
    #查询所有同学的id、姓名、选课的数量、总成绩；
    students = Student.objects.annotate(course_nums=Count('score__course'),total=Sum("score__number")).values('id','name','course_nums','total')
    #students = Student.objects.annotate(course_nums=Count('score'),total=Sum("score__number")).values('id','name','course_nums','total')

    for student in students:
        print(student)
    print(connection.queries)
    return HttpResponse("index1 ok")

def index2(request):
    #查询姓“李”的老师的个数
    count = Teacher.objects.filter(name__startswith='李').count()
    print(count)
    print(connection.queries)
    return HttpResponse("index2 ok")

def index3(request):
    #查询没学过“李老师”课的同学的id、姓名
    students = Student.objects.exclude(score__course__teacher__name='李老师').values('id','name')
    for student in students:
        print(student)
    print(connection.queries)
    return HttpResponse("index3 ok")

def index4(request):
    students = Student.objects.annotate(score_avg=Avg("score__number")).filter(score_avg__gt=60).values('id','score_avg')
    for student in students:
        print(student)
    print(connection.queries)
    return HttpResponse("index4 ok")

def index5(request):
    #查询学过课程id为1和2的所有同学的id、姓名；
    students = Student.objects.filter(score__course__id__in=[1,2,4]).values('id','name').distinct()
    for student in students:
        print(student)
    print(connection.queries)
    return HttpResponse("index5 ok")

def index6(request):
    #查询学过“黄老师”所教的“所有课”的同学的id、姓名；
    #1.找到每个学生学习的黄老师的课程的数量  a nums=Count("score__course",filter=Q(score__course__teacher__name="黄老师"))
    #2.在课程表中找到黄老师教的课的数量  b Course.objects.filter(teacher__name="黄老师").count()
    #3.如果a=b 说明学生学习了黄老师所有的课 如果不相等 说明没有学完

    #nums=Count("score__course",filter=Q(score__course__teacher__name="黄老师"))
    #nums1 = Course.objects.filter(teacher__name='黄老师').count()
    #Q()

    #students = Student.objects.annotate(nums=Count("score__course",filter=Q(score__course__teacher__name="黄老师"))).filter(nums=Course.objects.filter(teacher__name='黄老师').count()).values('id','name')
    # students = Student.objects.annotate(nums=Count("score__course",filter=Q(score__course__teacher__name="黄老师"))).filter().values('id','name','nums')
    #students = Course.objects.filter(teacher__name="黄老师").count()

    # for student in students:
    #     print(student)

    students = Student.objects.annotate(nums=Count("score__course",filter=Q(score__course__teacher__name="黄老师"))).filter(nums=Course.objects.filter(teacher__name='黄老师').count()).values('id','name')
    for student in students:
        print(student)
    print(connection.queries)
    return HttpResponse("index6 ok")